package org.example;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

public class ExcelProcessorThree {

    public static void main(String[] args) {
        String in = "C:\\Users\\luke\\Desktop\\2.5.xlsx";
        String out = "C:\\Users\\luke\\Desktop\\2.6\\";
        String sheetName = "Sheet1";
        System.out.println("->开始处理！" + sheetName);

        try {
            FileInputStream fis = new FileInputStream(in);
            Workbook workbook = WorkbookFactory.create(fis);
            Sheet sheet = workbook.getSheet(sheetName);

            if (sheet != null) {
                int rowCount = sheet.getLastRowNum();
                DataFormatter dataFormatter = new DataFormatter();

                // Using HashMap to store different D column values with corresponding workbooks
                Map<String, SXSSFWorkbook> workbookMap = new HashMap<>();

                for (int i = 1; i <= rowCount; i++) {
                    Row currentRow = sheet.getRow(i);
                    Cell currentCell = currentRow.getCell(12); // Get cell in column D (index 12)
                    String cellValue = dataFormatter.formatCellValue(currentCell);

                    if (!workbookMap.containsKey(cellValue)) {
                        // If the D column value does not have a corresponding workbook, create a new one
                        SXSSFWorkbook newWorkbook = new SXSSFWorkbook();
                        newWorkbook.createSheet("Sheet" + (i - 1));
                        workbookMap.put(cellValue, newWorkbook);
                    }
                }

                // Process the rows and copy data to corresponding workbooks
                for (int i = 1; i <= rowCount; i++) {
                    Row currentRow = sheet.getRow(i);
                    String cellValue = dataFormatter.formatCellValue(currentRow.getCell(12));
                    SXSSFWorkbook currentWorkbook = workbookMap.get(cellValue);
                    Sheet currentSheet = currentWorkbook.getSheetAt(0);
                    int newRowIdx = currentSheet.getLastRowNum() + 1;
                    Row newRow = currentSheet.createRow(newRowIdx);

                    for (int j = 0; j < currentRow.getLastCellNum(); j++) {
                        Cell currentSourceCell = currentRow.getCell(j);
                        Cell newCell = newRow.createCell(j);
                        newCell.setCellValue(dataFormatter.formatCellValue(currentSourceCell));

                        // Copy cell style
                        CellStyle newCellStyle = currentWorkbook.createCellStyle();
                        newCellStyle.cloneStyleFrom(currentSourceCell.getCellStyle());
                        newCell.setCellStyle(newCellStyle);
                    }
                }

                // Write different workbooks to separate files
                for (String cellValue : workbookMap.keySet()) {
                    String outputFile = out + cellValue + ".xlsx";
                    FileOutputStream fos = new FileOutputStream(outputFile);
                    SXSSFWorkbook currentWorkbook = workbookMap.get(cellValue);
                    currentWorkbook.write(fos);
                    fos.close();
                    currentWorkbook.close(); // Close SXSSFWorkbook to release temporary resources
                }

                System.out.println("->处理完成！");
            } else {
                System.out.println("->指定的表格不存在！");
            }

            workbook.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
